package database;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import model.Express;
import model.User;
import model.VerifyCode;

public class DatabaseBusiness {

	private static DatabaseBusiness dbb = new DatabaseBusiness();

	private DatabaseConnectionPool dbcp;

	public static DatabaseBusiness getInstance() {
		return dbb;
	}

	private DatabaseBusiness() {
		initialize();
	}

	private void initialize() {
		dbcp = DatabaseConnectionPool.getInstance();
	}

	public ResultSet executeQuery(String sql) {
		Connection conn = null;
		ResultSet rs = null;
		try {

			conn = dbcp.getConnection();
			if (conn == null) {
				return null;
			}

			Statement stmt = conn.createStatement();
			rs = stmt.executeQuery(sql);

		} catch (SQLException e) {
			System.out.println(e.getErrorCode() + ":" + e.getMessage());
		} finally {
			if (conn != null) {
				try {
					conn.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}

		return rs;
	}

	public boolean executeUpdate(String sql) {
		Connection conn = null;
		try {
			conn = dbcp.getConnection();
			if (conn == null) {
				return false;
			}

			Statement stmt = conn.createStatement();
			stmt.executeUpdate(sql);

		} catch (SQLException e) {
			System.out.println(e.getErrorCode() + ":" + e.getMessage());
			return false;
		} finally {
			if (conn != null) {
				try {
					conn.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
		return true;
	}
	
	/**
	 * @param name 使用一个user_name得到请求一个User对象
	 * @return 成功返回User对象/失败返回null
	 */
	public User getUser(String name) {
		ResultSet rs;
		User user = null;

		rs = executeQuery("SELECT * FROM user WHERE user.user_name = '"
				+ name + "' AND deleted = 0");

		if (rs == null) {
			return null;
		}

		try {
			if (rs.next()) {
				user = new User();
				user.setUid(rs.getInt(1));
				user.setName(rs.getString(2));
				user.setPassword(rs.getString(3));
				user.setType(rs.getInt(4));
				user.setInfo(rs.getString(5));
				user.setPhone(rs.getString(6));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}

		return user;
	}
	
	public Express getExpress(int dno){
		ResultSet rs;
		Express express = null;
		
		rs = executeQuery("SELECT * FROM doc WHERE doc.dno = " + dno);

		if (rs == null) {
			return null;
		}

		try {
			if (rs.next()) {
				express = new Express(
						rs.getInt(1),
						rs.getInt(2),
						rs.getString(3),
						rs.getString(4),
						rs.getString(5),
						rs.getString(6),
						rs.getInt(7),
						rs.getString(8),
						rs.getString(9),
						rs.getInt(10)
				);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		
		return express;
	}
	
	public VerifyCode getVcode(int vid){
		ResultSet rs;
		VerifyCode vcode = null;
		
		rs = executeQuery("SELECT * FROM verifycode WHERE verifycode.vid = " + vid);

		if (rs == null) {
			return null;
		}

		try {
			if (rs.next()) {
				vcode = new VerifyCode(
						rs.getInt(1),
						rs.getString(2),
						rs.getString(3)
				);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		
		return vcode;
	}

	public boolean addUser(String name, String password, int type, String phone, String info) {
		String sql = "INSERT INTO user (user_name, password, type, phone, info) VALUES ('" 
				+ name + "', '" + password + "', " + type + ", '" + phone + "', '" + info + "')";
		return executeUpdate(sql);
	}
	
	public boolean addLog(int uid, int action){
		String sql = "INSERT INTO log (action, uid) VALUES (" + action + "," + uid + ")";
		return executeUpdate(sql);
	}
	
	public boolean addVcode(String vcode){
		String sql = "INSERT INTO verifycode (vcode) VALUES ('"	+ vcode + "')";
		return executeUpdate(sql);
	}
	
	public boolean addExpress(Express express){
		// TODO: this
		String sql = "INSERT INTO doc (dno, address, addno, receiver, phone, status, poster_id) VALUES ("
				+ express.getDno() + ","
				+ express.getAddress() + ","
				+ express.getAddno() + ","
				+ ")";
		return executeUpdate(sql);
	}
	
	public static void main(String[] args) {
		/*
		 * test
		 */
		DatabaseBusiness dbb = DatabaseBusiness.getInstance();
		
		VerifyCode vcode = dbb.getVcode(1);
		if(vcode != null){
			System.out.println(vcode.getVid() + " " + vcode.getVcode() + " " + vcode.getCreateTime()); 
		}
		
		User u = dbb.getUser("test");
		if (u != null) {
			System.out.println(u.getName() + " " + u.getPassword());
		}
		
		//dbb.addUser("name", "password", 0, "18810310000", "informations");
		
		Express e = dbb.getExpress(123456);
		if (e != null) {
			System.out.println(e.getId() + " " + e.getAddress() + " " + e.getStartTime());
		}
		
	}
}
